package com.gaoyi.crm.db.dao;

import android.text.TextUtils;

import androidx.room.Dao;
import androidx.room.Query;
import androidx.room.RawQuery;
import androidx.room.Transaction;
import androidx.sqlite.db.SimpleSQLiteQuery;

import com.gaoyi.crm.db.CrmDatabase;
import com.gaoyi.crm.db.dto.PmnDto;
import com.gaoyi.crm.db.entity.Pmn;
import com.gaoyi.crm.db.entity.PmnWithAttach;
import com.gaoyi.crm.db.entity.PmnWithList;
import com.gaoyi.crm.db.query.MtcSyncedQuery;

import java.util.List;

@Dao
public abstract class PmnDao extends BaseDao<Pmn> {

//    @Query("SELECT id,code,custEnName AS custName,createdAt,modifyAt,syncedStatus,syncedAt FROM " + CrmDatabase.TABLE_PMN + " ORDER BY modifyAt DESC LIMIT :limit ")
//    public abstract List<PmnDto> getPmn(String custName, int limit);
//    @Query("SELECT pmn.id,pmn.code,pmn.createdAt,pmn.modifyAt,pmn.syncedAt,syncedStatus,:custName AS custName FROM " + CrmDatabase.TABLE_PMN + " AS pmn"
//            + " WHERE pmn.syncedStatus = :status"
//            + " AND (:keyword IS NULL OR :keyword ='' OR pmn.code LIKE '%' || :keyword || '%') "
//            + " AND (:startDate = 0 OR pmn.syncedAt >= :startDate) "
//            + " AND (:endDate = 0 OR pmn.syncedAt <= :endDate) "
//            + " ORDER BY CASE WHEN :orderByDate = 0 THEN pmn.syncedAt END DESC,CASE WHEN :orderByDate = 1 THEN pmn.syncedAt END ASC "
//            + " ,CASE WHEN :orderByCreateAt = 0 THEN pmn.createdAt END DESC,CASE WHEN :orderByCreateAt = 1 THEN pmn.createdAt END ASC "
//            + " ,CASE WHEN :orderByModifyAt = 0 THEN pmn.modifyAt END DESC,CASE WHEN :orderByModifyAt = 1 THEN pmn.modifyAt END ASC "
//            + " LIMIT :offset,:limit")
//    public abstract List<PmnDto> getPmn(String custName, int status, String keyword, long startDate, long endDate, int orderByDate, int orderByCreateAt, int orderByModifyAt, int offset, int limit);
//    @Query("SELECT COUNT(id) FROM " + CrmDatabase.TABLE_PMN
//            + " WHERE (:status IS NULL OR syncedStatus = :status)"
//            + " AND (:keyword IS NULL OR :keyword ='' OR code LIKE '%' || :keyword || '%') "
//            + " AND (:startDate = 0 OR syncedAt >= :startDate) "
//            + " AND (:endDate = 0 OR syncedAt <= :endDate) ")
//    public abstract int getPmnCountBySyncedAt(Integer status, String keyword, long startDate, long endDate);
//    @Query("SELECT COUNT(id) FROM " + CrmDatabase.TABLE_PMN
//            + " WHERE (:status IS NULL OR syncedStatus = :status)"
//            + " AND (:keyword IS NULL OR :keyword ='' OR code LIKE '%' || :keyword || '%' OR code LIKE '%' || :keyword || '%') "
//            + " AND (:startDate = 0 OR modifyAt >= :startDate) "
//            + " AND (:endDate = 0 OR modifyAt <= :endDate) ")
//    public abstract int getPmnCountByModifyAt(Integer status, String keyword, long startDate, long endDate);


    @RawQuery
    public abstract List<PmnDto> getPmn(SimpleSQLiteQuery query);

    @RawQuery
    public abstract List<PmnWithList> getNoSyncPmn(SimpleSQLiteQuery query);

    @RawQuery
    public abstract int getPmnCount(SimpleSQLiteQuery query);

    @Query("SELECT * FROM " + CrmDatabase.TABLE_PMN + " WHERE id = :id")
    public abstract Pmn getPmnById(String id);

    @Transaction
    @Query("SELECT * FROM " + CrmDatabase.TABLE_PMN + " WHERE id = :id")
    public abstract PmnWithAttach getPmnAllById(String id);

    @Query("SELECT id FROM " + CrmDatabase.TABLE_PMN + " WHERE code = :code")
    public abstract String getPmnByCode(String code);

    @Query("DELETE FROM " + CrmDatabase.TABLE_PMN + " WHERE id = :id")
    public abstract void deleteById(String id);

    @Query("DELETE FROM " + CrmDatabase.TABLE_PMN + " WHERE id IN (:ids)")
    public abstract void deleteByIds(List<String> ids);

    @Query("UPDATE " + CrmDatabase.TABLE_PMN + " SET syncedAt = :syncAt,syncedStatus = :status WHERE id = :pmnId")
    public abstract void updateStatusById(String pmnId, long syncAt, int status);

    @Query("UPDATE " + CrmDatabase.TABLE_PMN + " SET syncedAt = :syncAt,syncedStatus = :status WHERE id IN (:pmnIds)")
    public abstract void updateStatusByIds(List<String> pmnIds, long syncAt, int status);

    public SimpleSQLiteQuery getLimitPmnSql(String custName, int limit) {
        String sb = "SELECT id,code," + custName + " AS custName,createdAt,modifyAt,syncedStatus,syncedAt FROM " + CrmDatabase.TABLE_PMN +
                " ORDER BY modifyAt DESC LIMIT " + limit;
        return new SimpleSQLiteQuery(sb);
    }

    public SimpleSQLiteQuery getPmnSqlBySyncedAt(String custName, MtcSyncedQuery query, int offset, int limit) {
        return getPmnSql(custName, "syncedAt", query, offset, limit);
    }

    public SimpleSQLiteQuery getPmnSqlByModifyAt(String custName, MtcSyncedQuery query, int offset, int limit) {
        return getPmnSql(custName, "modifyAt", query, offset, limit);
    }

    public SimpleSQLiteQuery getPmnSql(String custName, String queryDate, MtcSyncedQuery query, int offset, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT id,code,createdAt,modifyAt,syncedAt,syncedStatus,").append(custName).append(" AS custName FROM ").append(CrmDatabase.TABLE_PMN).append(" WHERE 1 = 1");
        if (query.getSyncedStatus() != null) {
            sb.append(" AND syncedStatus = ").append(query.getSyncedStatus());
        }
        if (!TextUtils.isEmpty(query.getKeyWord())) {
            sb.append(" AND (code LIKE '%").append(query.getKeyWord()).append("%' OR custName LIKE '%").append(query.getKeyWord()).append("%')");
        }
        if (query.getStartDate() != 0) {
            sb.append(" AND ").append(queryDate).append(" >= ").append(query.getStartDate());
        }
        if (query.getEndDate() != 0) {
            sb.append(" AND ").append(queryDate).append(" <= ").append(query.getEndDate());
        }
        boolean hasOrderBy = false;
        if (query.getOrderBySyncedAt() != null) {
            sb.append(" ORDER BY syncedAt ").append(query.getOrderBySyncedAt() == 0 ? "DESC" : "ASC");
            hasOrderBy = true;
        }
        if (query.getOrderByCreatedAt() != null) {
            sb.append(hasOrderBy ? " ," : " ORDER BY ");
            sb.append(" createdAt ").append(query.getOrderByCreatedAt() == 0 ? "DESC" : "ASC");
            hasOrderBy = true;
        }
        if (query.getOrderByModifyAt() != null) {
            sb.append(hasOrderBy ? " ," : " ORDER BY ");
            sb.append(" modifyAt ").append(query.getOrderByModifyAt() == 0 ? "DESC" : "ASC");
            hasOrderBy = true;
        }
        if (query.getOrderBySyncStatus() != null) {
            sb.append(hasOrderBy ? " ," : " ORDER BY ");
            sb.append(" syncedStatus ").append(query.getOrderBySyncStatus() == 0 ? "DESC" : "ASC");
        }
        sb.append(" LIMIT ").append(offset).append(",").append(limit);
        return new SimpleSQLiteQuery(sb.toString());
    }

    public SimpleSQLiteQuery getPmnCountBySyncedAtSql(String custName, Integer status, String keyword, long startDate, long endDate) {
        return getPmnCountSql(custName, "syncedAt", status, keyword, startDate, endDate);
    }

    public SimpleSQLiteQuery getPmnCountByModifyAtSql(String custName, Integer status, String keyword, long startDate, long endDate) {
        return getPmnCountSql(custName, "modifyAt", status, keyword, startDate, endDate);
    }

    public SimpleSQLiteQuery getPmnCountSql(String custName, String queryDate, Integer status, String keyword, long startDate, long endDate) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT COUNT(id) FROM ").append(CrmDatabase.TABLE_PMN).append(" WHERE 1 = 1");
        if (status != null) {
            sb.append(" AND syncedStatus = ").append(status);
        }
        if (!TextUtils.isEmpty(keyword)) {
            sb.append(" AND (code LIKE '%").append(keyword).append("%' OR ").append(custName).append(" LIKE '%").append(keyword).append("%')");
        }
        if (startDate != 0) {
            sb.append(" AND ").append(queryDate).append(" >= ").append(startDate);
        }
        if (endDate != 0) {
            sb.append(" AND ").append(queryDate).append(" <= ").append(endDate);
        }
        return new SimpleSQLiteQuery(sb.toString());
    }

    public SimpleSQLiteQuery getNoSyncPmnSql(String custName, MtcSyncedQuery query, int offset, int limit) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT * FROM ").append(CrmDatabase.TABLE_PMN).append(" WHERE 1 = 1");
        if (query.getSyncedStatus() != null) {
            sb.append(" AND syncedStatus = ").append(query.getSyncedStatus());
        }
        if (!TextUtils.isEmpty(query.getKeyWord())) {
            sb.append(" AND (code LIKE '%").append(query.getKeyWord()).append("%' OR ").append(custName).append(" LIKE '%").append(query.getKeyWord()).append("%')");
        }
        if (query.getStartDate() != 0) {
            sb.append(" AND modifyAt >= ").append(query.getStartDate());
        }
        if (query.getEndDate() != 0) {
            sb.append(" AND modifyAt <= ").append(query.getEndDate());
        }
        boolean hasOrderBy = false;
        if (query.getOrderByCreatedAt() != null) {
            sb.append(" ORDER BY createdAt ").append(query.getOrderByCreatedAt() == 0 ? "DESC" : "ASC");
            hasOrderBy = true;
        }
        if (query.getOrderByModifyAt() != null) {
            sb.append(hasOrderBy ? " ," : " ORDER BY ");
            sb.append(" modifyAt ").append(query.getOrderByModifyAt() == 0 ? "DESC" : "ASC");
        }
        sb.append(" LIMIT ").append(offset).append(",").append(limit);
        return new SimpleSQLiteQuery(sb.toString());
    }

}
